import enum
from datetime import date
from decimal import Decimal
from typing import Optional, List

from sqlalchemy import String, DECIMAL, Boolean, func, insert, select, text, update, delete, and_, or_, ForeignKey, \
    Table, Column, extract, desc
from sqlalchemy.orm import Mapped, mapped_column, sessionmaker, relationship

from ch04.db_main import Base, engine
from ch04.emp_manager.models import Employee, SexValue


class Dept(Base):
    """部门的模型类"""
    __tablename__ = 't_dept'
    __table_args__ = {'extend_existing': True}

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(20), name='dname', unique=True, nullable=False)
    city: Mapped[str] = mapped_column(String(50))

    # 定义一个关联属性：一个部门下的所有员工
    emp_list: Mapped[List['Employee']] = relationship(back_populates='dept', cascade='save-update')

    # 定义一个外键：关联到父机构
    pid: Mapped[Optional[int]] = mapped_column(ForeignKey('t_dept.id'), nullable=True)

    # 定义一个关联属性
    children: Mapped[List['Dept']] = relationship(back_populates='parent')
    # 定义一个关联属性
    parent: Mapped[Optional['Dept']] = relationship(back_populates='children', remote_side=[id])

    def __str__(self):
        return f'{self.name}, {self.id}, {self.city}'


class IdCard(Base):
    """身份证的模型类，它和员工之间是一对一的关联关系"""

    __tablename__ = 't_id_card'
    __table_args__ = {'extend_existing': True}
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    card_number: Mapped[str] = mapped_column(String(18), unique=True, nullable=False, comment='身份证号码')
    origin: Mapped[Optional[str]] = mapped_column(String(20), nullable=True, comment='籍贯')

    emp_id: Mapped[int] = mapped_column(ForeignKey('t_emp.id'))

    # 一对一的关联属性
    emp: Mapped['Employee'] = relationship(single_parent=True, back_populates='idc')


# 多对多关联，先定义中间表
middle_table = Table(
    't_user_role',
    Base.metadata,
    Column('user_id', ForeignKey('t_user.id'), primary_key=True),
    Column('role_id', ForeignKey('t_role.id'), primary_key=True) , # 联合主键
    extend_existing = True  # 添加 extend_existing 参数
)

class User(Base):
    """用户模型类，它和角色之间是多对多关联"""
    __tablename__ = 't_user'
    __table_args__ = {'extend_existing': True}
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    username: Mapped[str] = mapped_column(String(20), unique=True, nullable=False, comment='用户名')
    password: Mapped[str] = mapped_column(String(20), nullable=False, comment='密码')

    # 多对多的关联属性
    roles: Mapped[Optional[List['Role']]] = relationship(secondary=middle_table, back_populates='users')

class Role(Base):
    """角色模型类，它和用户之间是多对多关联"""
    __tablename__ = 't_role'
    __table_args__ = {'extend_existing': True}
    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    name: Mapped[str] = mapped_column(String(20), unique=True, nullable=False, comment='角色名字')

    # 多对多的关联属性
    users: Mapped[Optional[List['User']]] = relationship(secondary=middle_table, back_populates='roles')


if __name__ == '__main__':
    with sessionmaker(engine).begin() as session:
        # 新增
        dept = Dept(name='北京总公司', city='北京')
        emp1 = Employee(name='aaa', sal=4000, bonus=800, gender=SexValue.MALE, entry_date=date(2019, 10, 23))
        emp2 = Employee(name='bbb', sal=7000, bonus=900, gender=SexValue.MALE, entry_date=date(2018, 8, 23))

        dept.emp_list = [emp1, emp2]  # emp1,emp2 都放到dept部门下

        session.add(dept)

        # 修改：给张三设置一个部门为：北京总公司
        # emp = session.get(Employee, 1)
        # dept = session.get(Dept, 1)
        # # emp.dept = dept
        # emp.dept_id = dept.id

        # 查询
        # dept = session.get(Dept, 1)
        # print(dept.name)
        #
        # # 打印当前部门下的所有员工
        # for emp in dept.emp_list:
        #     print(emp)

        # emp = session.get(Employee, 1)
        # 打印该员工的所属部门名字
        # print(emp.dept.name)

        # 删除, 设置张三的部门为空
        # emp.dept = None
        # session.execute(update(Employee).where(Employee.id == 1).values(dept_id=None))

        # 插入部门
        # d1 = Dept(name='行政部', pid=1, city='beijing')
        # d2 = Dept(name='湖南分公司', pid=1, city='长沙')
        # d3 = Dept(name='销售部', parent=d2, city='长沙')
        # #
        # session.add(d1)
        # session.add(d2)


        # 给张三新增一个身份证
        # idc = IdCard(card_number='22222222222', emp_id=1)
        # session.add(idc)

        # emp = session.get(Employee, 1)
        # print(emp)
        # print(emp.idc.card_number)

        # 多对多的操作测试
        # u1 = User(username='zs', password='123123')
        # u2 = User(username='ls', password='123123')
        #
        # r1 = Role(name='仓库管理员')
        # r2 = Role(name='系统管理员')
        #
        # # zs用户有两个角色， ls用户只有一个角色
        # u1.roles = [r1, r2]
        # u2.roles = [r2]
        #
        # session.add(u1)

        user = session.get(User, 1)
        print(user.roles[0].name)
        print(user.roles[1].name)

        # result = session.execute(select(Employee.name, Dept.name.label('dname')).join(Dept, isouter=True).where(extract('year', Employee.entry_date) == 2018))
        # result = session.execute(select(Employee.name, Dept.name).join(Dept, isouter=True).join(IdCard).where(IdCard.card_number == '111111111111'))

        # Employee.query
        # result = session.execute(select(Dept, func.count(Employee.dept_id).label('ec')).join(Employee, isouter=True).group_by(Dept.id).order_by(desc('ec')))
        # sub_stmt = select(User.username.label('username'), func.count(Role.id).label('role_count')).join(User.roles).group_by(User.id).subquery()
        # result = session.execute(select(sub_stmt.c.username, sub_stmt.c.role_count).where(sub_stmt.c.role_count > 1))
        #
        # for o in result:
        #     print(type(o))
        #     print(o[0], o[1])

        # 关联查询
        # 1、查询2018年入职的员工姓名以及该员工的所在部门名称
        # stmt = select(Employee.name.label('ename'), Dept.name.label('dname')).join(Dept, isouter=True).where(extract('year', Employee.entry_date) == 2018)

        # 2、 查询身份号码是：11111111111。 的员工的姓名，以及该员工所属的部门
        # stmt = select(Employee.name, Dept.name).join(Dept, isouter=True).join(IdCard).where(IdCard.card_number == '111111111111')

        # 3、 查询，每个部门名字、city以及该部门下面的员工个数
        # stmt = select(Dept, func.count(Employee.id)).join(Employee, isouter=True).group_by(Dept.id)

        # 4、查询拥有角色数量超过1的 用户名以及他所拥有的角色数量。
        # sub_stmt = select(User.username, func.count(Role.id).label('role_count')).join(User.roles).group_by(User.id).subquery()
        # stmt = select(sub_stmt.c.username, sub_stmt.c.role_count).where(sub_stmt.c.role_count > 1)

        # 5、 查询每个部门的名字以及它里面的员工数量，并且按照员工数量的个数降序排序。
        # stmt = select(Dept.name, func.count(Employee.id).label('emp_count')).join(Employee, isouter=True).group_by(Dept.id).order_by(desc('emp_count'))
        # result = session.execute(stmt)
        # for obj in result:
        #     print(obj[0], obj[1])